package com.wuda.dao;

import com.wuda.bean.User;
import com.wuda.utils.Base;
import com.wuda.utils.Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao {

	/**
	 * 查找用户
	 * @param email 邮箱
	 * @return User
	 */
	public User findUserByEmail(String email) throws SQLException, ClassNotFoundException {
		Connection connection = null;
		ResultSet resultSet = null;
		User user = null;
		try {
			connection = Base.getConnection();
			String sql = "SELECT * FROM user WHERE email = ?";
			resultSet = Base.executeQuery(connection, sql, new Object[]{email});

			if (resultSet.next()) {
				user = new User();
				user.setUserId(resultSet.getInt("user_id"));
				user.setUsername(resultSet.getString("username"));
				user.setPassword(resultSet.getString("password"));
				user.setEmail(resultSet.getString("email"));
				user.setRole(resultSet.getString("role"));
				user.setStatus(resultSet.getString("status"));
			}
		} finally {
			Base.closeResource(connection, null, resultSet);
		}
		return user;
	}

	/**
	 * 保存用户
	 * @param user 用户参数
	 * @return 结果
	 */
	public boolean saveUser(User user) throws SQLException, ClassNotFoundException {
		Connection connection = null;
		try {
			connection = Base.getConnection();
			String sql = "INSERT INTO user (username, password, email) VALUES (?, ?, ?)";
			int result = Base.executeUpdate(connection, sql, new Object[]{
					user.getUsername(),
					user.getPassword(),
					user.getEmail()
			});
			return result > 0;
		} finally {
			Base.closeResource(connection, null, null);
		}
	}

	/**
	 * 判断邮箱是否已存在
	 * @param email 邮箱
	 * @return  boolean
	 */
	public boolean isEmailExists(String email) throws SQLException, ClassNotFoundException {
		Connection connection = null;
		ResultSet resultSet = null;
		try {
			connection = Base.getConnection();
			String sql = "SELECT COUNT(*) FROM user WHERE email = ?";
			resultSet = Base.executeQuery(connection, sql, new Object[]{email});

			if (resultSet.next()) {
				return resultSet.getInt(1) > 0;
			}
			return false;
		} finally {
			Base.closeResource(connection, null, resultSet);
		}
	}

	/**
	 * 获取所有用户信息，排除密码列
	 * @param username 用户名
	 * @param limit 每页显示条数
	 * @param offset 偏移量
	 * @return List<User>
	 */
	public List<User> getAllUsers(String username, int limit, int offset) throws SQLException, ClassNotFoundException {
		List<User> users = new ArrayList<>();
		Connection connection = null;
		ResultSet resultSet = null;
		try {
			connection = Base.getConnection();
			StringBuilder sql = new StringBuilder("SELECT user_id, username, email, role, status FROM user ");
			if (username != null && !username.trim().isEmpty()) {
				sql.append("WHERE username LIKE ? ");
			}
			sql.append("LIMIT ? OFFSET ?");
			PreparedStatement pstmt = connection.prepareStatement(sql.toString());
			int paramIndex = 1;
			if (username != null && !username.trim().isEmpty()) {
				pstmt.setString(paramIndex++, "%" + username + "%");
			}
			pstmt.setInt(paramIndex++, limit);
			pstmt.setInt(paramIndex, offset);

			resultSet = pstmt.executeQuery();

			while (resultSet.next()) {
				User user = new User();
				user.setUserId(resultSet.getInt("user_id"));
				user.setUsername(resultSet.getString("username"));
				user.setEmail(resultSet.getString("email"));
				user.setRole(resultSet.getString("role"));
				user.setStatus(resultSet.getString("status"));
				users.add(user);
			}
		} finally {
			Base.closeResource(connection, null, resultSet);
		}
		return users;
	}

	/**
	 * 获取用户总数
	 * @param username 用户名
	 * @return 用户总数
	 */
	public int getUserCount(String username) throws SQLException, ClassNotFoundException {
		Connection connection = null;
		ResultSet resultSet = null;
		try {
			connection = Base.getConnection();
			StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM user ");
			if (username != null && !username.trim().isEmpty()) {
				sql.append("WHERE username LIKE ?");
			}
			PreparedStatement pstmt = connection.prepareStatement(sql.toString());
			if (username != null && !username.trim().isEmpty()) {
				pstmt.setString(1, "%" + username + "%");
			}
			resultSet = pstmt.executeQuery();
			if (resultSet.next()) {
				return resultSet.getInt(1);
			}
			return 0;
		} finally {
			Base.closeResource(connection, null, resultSet);
		}
	}


	/**
	 * 更新用户名
	 * @param userId 用户id
	 * @param newUsername 新用户名
	 * @return boolean
	 */
	public boolean updateUsername(int userId, String newUsername) {
		Connection connection = null;

		try {
			connection = Base.getConnection();
			String sql = "UPDATE user SET username = ? WHERE user_id = ?";
			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, newUsername);
			pstmt.setInt(2, userId);

			int rowsUpdated = pstmt.executeUpdate();
			return rowsUpdated > 0;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				Base.closeResource(connection, null, null);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 更新用户密码
	 * @param userId 用户id
	 * @param newPassword 新密码
	 * @return boolean
	 */
	public boolean updatePassword(int userId, String newPassword) {
		Connection connection = null;

		try {
			connection = Base.getConnection();
			String sql = "UPDATE user SET password = ? WHERE user_id = ?";
			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, Util.passMd5(newPassword));
			pstmt.setInt(2, userId);

			int rowsUpdated = pstmt.executeUpdate();
			return rowsUpdated > 0;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				Base.closeResource(connection, null, null);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}


	/**
	 * 根据用户ID查找用户
	 * @param userId 用户id
	 * @return User对象
	 */
	public User getUserById(Integer userId){
		Connection connection = null;
		ResultSet resultSet = null;
		User user = null;

		try {
			connection = Base.getConnection();
			String sql = "SELECT user_id, username, email FROM user WHERE user_id = ?";
			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.setInt(1, userId);

			resultSet = pstmt.executeQuery();
			if (resultSet.next()) {
				user = new User();
				user.setUserId(resultSet.getInt("user_id"));
				user.setUsername(resultSet.getString("username"));
				user.setEmail(resultSet.getString("email"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				Base.closeResource(connection, null, resultSet);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return user;
	}


	/**
	 * 根据用户ID查找用户密码
	 * @param userId 用户id
	 * @return User对象
	 */
	public User getUserPasswordById(Integer userId){
		Connection connection = null;
		ResultSet resultSet = null;
		User user = null;
		try {
			connection = Base.getConnection();
			String sql = "SELECT user_id, password FROM user WHERE user_id = ?";
			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.setInt(1, userId);

			resultSet = pstmt.executeQuery();
			if (resultSet.next()) {
				user = new User();
				user.setUserId(resultSet.getInt("user_id"));
				user.setPassword(resultSet.getString("password"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				Base.closeResource(connection, null, resultSet);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return user;
	}


	/**
	 * 删除用户
	 *
	 * @param userId 用户ID
	 * @return boolean 是否成功
	 * @throws SQLException, ClassNotFoundException
	 */
	public boolean deleteUser(int userId) throws SQLException, ClassNotFoundException {
		Connection connection = null;
		try {
			connection = Base.getConnection();
			String sql = "DELETE FROM user WHERE user_id = ?";
			Object[] params = { userId };
			int result = Base.executeUpdate(connection, sql, params);
			return result > 0;
		} finally {
			Base.closeResource(connection, null, null);
		}
	}

	/**
	 * 禁用或激活用户
	 *
	 * @param userId 用户ID
	 * @param status 用户状态
	 * @return boolean 是否成功
	 * @throws SQLException, ClassNotFoundException
	 */
	public boolean disableOrActiveUser(int userId, String status) throws SQLException, ClassNotFoundException {
		Connection connection = null;
		try {
			connection = Base.getConnection();
			String sql = "UPDATE user SET status = ? WHERE user_id = ?";
			Object[] params = { status, userId };
			int result = Base.executeUpdate(connection, sql, params);
			return result > 0;
		} finally {
			Base.closeResource(connection, null, null);
		}
	}

}
